﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace App_Code
{
    public class CaptialService : CaptialIService
    {
        //accepts no parameters
        //returns list of sensor objects
        [WebInvoke(Method = "GET", BodyStyle = WebMessageBodyStyle.Bare, RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json)]
        public List<Capital> GetCapitals(string spatialCoords)
        {
            var capitalList = new List<Capital>();
            try
            {
                using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
                {
                    connection.Open();
                    string sqlString = "SET @bbox = 'POLYGON((" + spatialCoords + "))';"; 
                    sqlString += " SELECT city, state FROM capitals WHERE Intersects( coords, GeomFromText(@bbox) );";

                    //execute query
                    MySqlCommand command = connection.CreateCommand();
                    command.CommandText = sqlString;                    
                    MySqlDataReader sqlReader = command.ExecuteReader();

                    //create list
                    while (sqlReader.Read())
                    {
                        var capital = new Capital();
                        capital.city = sqlReader["city"].ToString();
                        capital.state = sqlReader["state"].ToString();
                        capitalList.Add(capital);
                    }
                }
            }
            catch (Exception ex)
            {
                var capital = new Capital();
                capital.city = ex.Message;
                capital.state = ex.Message;
                capitalList.Add(capital);
            }
            return capitalList;
        }
    }
}